
ALTER  proc sp_create_update_inventory_lot_for_finished_goods (

@process_step_id int, 
@location_id int,
@lot_number varchar(50), 
@qty_in int,
@qty_out int,
@date_in datetime,
@date_out datetime)

as
begin

	if @date_in = '01/01/1900' 
	begin
		set @date_in = null
	end

	if @date_out = '01/01/1900' 
	begin
		set @date_out = null
	end
	if @qty_out = 0
	begin
		set @qty_out = null
	end

	declare @lot_current_status varchar(50)
	declare @normal_cost float
	declare @lot_id int

	Select @lot_current_status = Lot_Current_Status, @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	
	if @lot_id is null -- Check if the lot exists
		begin -- Create Lot
			select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
			insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP)
			select @lot_id = @@IDENTITY				
		end
	
	else 
		begin
			update Lot_Table set Lot_Current_Status = 'Running' where Lot_ID = @lot_id
			update Inventory_Lot set IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_Balance = null, Timestamp=Current_Timestamp where Lot_ID = @lot_id
			return
		end

	-- insert/update  Inventory Lot
	declare @inv_lot_id int
	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
	if @inv_lot_id is null
		begin
			--update Lot_Table set Lot_Current_Status = 'Running' where Lot_ID = @lot_id
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out,Timestamp) values (@lot_id, @qty_in, @qty_out, @date_in, @date_out,CURRENT_TIMESTAMP)
		end
	else
	begin
		-- dummy
		update Lot_Table set Lot_Current_Status = 'Running' where Lot_ID = @lot_id
		update Inventory_Lot set IL_Qty_In = @qty_in where Lot_ID = @inv_lot_id
	end
end
